package ac.neec.db.service;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.List;

import ac.neec.data.MajorHeading;
import ac.neec.data.RecordDate;
import ac.neec.data.RecordDetail;
import ac.neec.data.SpecificActivities;
import ac.neec.listener.ProgressListener;
import android.content.ContentValues;
import android.content.Context;
import android.content.res.AssetManager;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DBOpenHelper extends SQLiteOpenHelper {
	private static final String TB_MAJOR_HEADING = "major_heading";
	private static final String TB_SPECIFIC_ACTIVITIES = "specific_activities";
	private static final String TB_RECORD_DATE = "record_date";
	private static final String TB_RECORD_DETAIL = "record_detail";
	private SQLiteDatabase db;
	private final static String DBNAME = "mio.db";
	private final static int DBVER = 1;
	private Context mContext;

	public DBOpenHelper(Context context) {
		super(context, DBNAME, null, DBVER);
		mContext = context;
		db = getWritableDatabase();
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		try {
			execCreateSql(db, "sqlite/create");
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	}

	public boolean drop() {
		db.execSQL("DROP TABLE " + DBNAME + ";");
		return true;
	}

	public void execInsertSql(ProgressListener listener) throws IOException {
		AssetManager as = mContext.getResources().getAssets();
		try {
			SQLiteDatabase db = getWritableDatabase();
			String files[] = as.list("sqlite/insert");
			int len = 0;
			for (int i = 0; i < files.length; i++) {
				String str = readFile(as.open("sqlite/insert" + "/" + files[i]));
				len += str.split("/").length;
			}
			listener.notofyMaxNumeric(len);

			int count = 0;
			for (int i = 0; i < files.length; i++) {
				String str = readFile(as.open("sqlite/insert" + "/" + files[i]));
				for (String sql : str.split("\n")) {
					db.execSQL(sql);
					listener.notifyNowNumeric(++count);
				}
			}
			listener.notifyComplete();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	private void execCreateSql(SQLiteDatabase db, String assetsDir) throws IOException {
		AssetManager as = mContext.getResources().getAssets();
		try {
			String files[] = as.list(assetsDir);
			for (int i = 0; i < files.length; i++) {
				String str = readFile(as.open(assetsDir + "/" + files[i]));
				for (String sql : str.split("/")) {
					db.execSQL(sql);
				}
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	private String readFile(InputStream is) throws IOException {
		BufferedReader br = null;
		try {
			br = new BufferedReader(new InputStreamReader(is, "UTF-8"));
			StringBuilder sb = new StringBuilder();
			String str;
			while ((str = br.readLine()) != null) {
				sb.append(str + "\n");
			}
			return sb.toString();
		} finally {
			if (br != null)
				br.close();
		}
	}

	//---SQLite Drivers--------------
	public List<MajorHeading> selectMajorHeadingAll() {
		List<MajorHeading> list = new ArrayList<MajorHeading>();
		String[] cullom = new String[] {
				"heading_id", "title_jp", "title_eng"
		};
		Cursor cursor;
		cursor = db.query(TB_MAJOR_HEADING, cullom,
				null, null, null, null, null);
		cursor.moveToFirst();
		boolean flag = true;
		if (cursor.isLast() || cursor.getCount() < 1)
			flag = false;
		while (flag) {
			int index_heading_id = cursor.getColumnIndex(cullom[0]);
			int index_title_jp = cursor.getColumnIndex(cullom[1]);
			int index_title_eng = cursor.getColumnIndex(cullom[2]);
			int headingID = cursor.getInt(index_heading_id);
			String titleJP = cursor.getString(index_title_jp);
			String titleENG = cursor.getString(index_title_eng);
			list.add(new MajorHeading(headingID, titleJP, titleENG));
			if (cursor.isLast())
				flag = false;
			cursor.moveToNext();
		}
		cursor.close();
		return list;
	}

	public List<SpecificActivities> selectSpecificActivitiesAll() {
		List<SpecificActivities> list = new ArrayList<SpecificActivities>();
		String[] cullom = new String[] {
				"activities_id", "heading_id", "detail_jp", "detail_eng", "mets"
		};
		Cursor cursor;
		cursor = db.query(TB_SPECIFIC_ACTIVITIES, cullom,
				null, null, null, null, null);
		cursor.moveToFirst();
		boolean flag = true;
		if (cursor.isLast() || cursor.getCount() < 1)
			flag = false;
		while (flag) {
			int index_activities_id = cursor.getColumnIndex(cullom[0]);
			int index_heading_id = cursor.getColumnIndex(cullom[1]);
			int index_detail_jp = cursor.getColumnIndex(cullom[2]);
			int index_detail_eng = cursor.getColumnIndex(cullom[3]);
			int index_mets = cursor.getColumnIndex(cullom[4]);

			int activitiesID = cursor.getInt(index_activities_id);
			int headingID = cursor.getInt(index_heading_id);
			String detailJP = cursor.getString(index_detail_jp);
			String detailENG = cursor.getString(index_detail_eng);
			float mets = cursor.getFloat(index_mets);

			list.add(new SpecificActivities(activitiesID, headingID, detailJP, detailENG, mets));
			if (cursor.isLast())
				flag = false;
			cursor.moveToNext();
		}
		cursor.close();
		return list;
	}

	public List<RecordDate> selectRecordDateAll() {
		List<RecordDate> list = new ArrayList<RecordDate>();
		String[] cullom = new String[] {
				"date_id", "date_cullom"
		};
		Cursor cursor;
		cursor = db.query(TB_RECORD_DATE, cullom,
				null, null, null, null, null);
		cursor.moveToFirst();
		boolean flag = true;
		if (cursor.getCount() < 1)
			flag = false;
		while (flag) {
			int index_date_id = cursor.getColumnIndex(cullom[0]);
			int index_date = cursor.getColumnIndex(cullom[1]);

			int dateID = cursor.getInt(index_date_id);
			String date = cursor.getString(index_date);

			list.add(new RecordDate(dateID, date));
			if (cursor.isLast())
				flag = false;
			cursor.moveToNext();
		}
		cursor.close();
		return list;
	}

	public List<RecordDetail> selectRecordDetailAll() {
		List<RecordDetail> list = new ArrayList<RecordDetail>();
		String[] cullom = new String[] {
				"detail_id", "date_id", "activities_id","measurement_time", "heart_rate_theory", "heart_rate_point",
				"calorie_theory", "calorie_point", "graph"
		};
		Cursor cursor;
		cursor = db.query(TB_RECORD_DETAIL, cullom,
				null, null, null, null, null);
		cursor.moveToFirst();
		boolean flag = true;
		if (cursor.getCount() < 1)
			flag = false;
		while (flag) {
			int index_det_id = cursor.getColumnIndex(cullom[0]);
			int index_date_id = cursor.getColumnIndex(cullom[1]);
			int index_act_id = cursor.getColumnIndex(cullom[2]);
			int index_time = cursor.getColumnIndex(cullom[3]);
			int index_heart_t = cursor.getColumnIndex(cullom[4]);
			int index_heart_p = cursor.getColumnIndex(cullom[5]);
			int index_cal_t = cursor.getColumnIndex(cullom[6]);
			int index_cal_p = cursor.getColumnIndex(cullom[7]);
			int index_graph = cursor.getColumnIndex(cullom[8]);

			int detID = cursor.getInt(index_det_id);
			int dateID = cursor.getInt(index_date_id);
			int actID = cursor.getInt(index_act_id);
			String time = cursor.getString(index_time);
			int heart_t = cursor.getInt(index_heart_t);
			int heart_p = cursor.getInt(index_heart_p);
			float cal_t = cursor.getFloat(index_cal_t);
			float cal_p = cursor.getFloat(index_cal_p);
			byte[] graph = cursor.getBlob(index_graph);

			RecordDetail detail = new RecordDetail(detID, dateID, actID, time, heart_t, heart_p, cal_t, cal_p, graph);
			list.add(detail);
			if (cursor.isLast())
				flag = false;
			cursor.moveToNext();
		}
		cursor.close();
		return list;
	}

	public List<List<SpecificActivities>> selectSpecificActivitiesCategories() {
		List<List<SpecificActivities>> list = new ArrayList<List<SpecificActivities>>();
		String[] cullom = new String[] {
				"activities_id", "heading_id", "detail_jp", "detail_eng", "mets"
		};
		Cursor cursor;
		cursor = db.query(TB_SPECIFIC_ACTIVITIES, cullom,
				null, null, null, null, null);
		cursor.moveToFirst();
		boolean flag = true;
		int beforeID = 0;
		List<SpecificActivities> row = new ArrayList<SpecificActivities>();
		if (cursor.isLast() || cursor.getCount() < 1)
			flag = false;
		while (flag) {
			int index_activities_id = cursor.getColumnIndex(cullom[0]);
			int index_heading_id = cursor.getColumnIndex(cullom[1]);
			int index_detail_jp = cursor.getColumnIndex(cullom[2]);
			int index_detail_eng = cursor.getColumnIndex(cullom[3]);
			int index_mets = cursor.getColumnIndex(cullom[4]);

			int activitiesID = cursor.getInt(index_activities_id);
			int headingID = cursor.getInt(index_heading_id);
			String detailJP = cursor.getString(index_detail_jp);
			String detailENG = cursor.getString(index_detail_eng);
			float mets = cursor.getFloat(index_mets);

			if (beforeID != headingID) {
				list.add(row);
				row = new ArrayList<SpecificActivities>();
				beforeID = headingID;
			}
			row.add(new SpecificActivities(activitiesID, headingID, detailJP, detailENG, mets));
			if (cursor.isLast())
				flag = false;
			cursor.moveToNext();
		}
		list.add(row);
		cursor.close();
		return list;
	}

	public MajorHeading selectMajorHeadingMatch(int paramHeadingID) {
		String[] cullom = new String[] {
				"heading_id", "title_jp", "title_eng"
		};
		String sql = "SELECT heading_id, title_jp, title_eng FROM major_heading WHERE heading_id = ?";
		Cursor cursor = db.rawQuery(sql, new String[] { String.valueOf(paramHeadingID) });
		cursor.moveToFirst();
		if (cursor.getCount() < 1)
			return null;
		int index_heading_id = cursor.getColumnIndex(cullom[0]);
		int index_title_jp = cursor.getColumnIndex(cullom[1]);
		int index_title_eng = cursor.getColumnIndex(cullom[2]);
		int headingID = cursor.getInt(index_heading_id);
		String titleJP = cursor.getString(index_title_jp);
		String titleENG = cursor.getString(index_title_eng);

		cursor.close();
		return new MajorHeading(headingID, titleJP, titleENG);
	}

	public SpecificActivities selectSpecificActivitiesMatch(int paramActivitiesID) {
		String[] cullom = new String[] {
				"activities_id", "heading_id", "detail_jp", "detail_eng", "mets"
		};
		String sql = String.format("SELECT %s, %s, %s, %s, %s FROM %s WHERE %s = ?",
				cullom[0], cullom[1], cullom[2], cullom[3], cullom[4],
				TB_SPECIFIC_ACTIVITIES, cullom[0]);
		Cursor cursor = db.rawQuery(sql, new String[] { String.valueOf(paramActivitiesID) });
		cursor.moveToFirst();
		if (cursor.getCount() < 1)
			return null;
		int index_act_id = cursor.getColumnIndex(cullom[0]);
		int index_head_id = cursor.getColumnIndex(cullom[1]);
		int index_det_jp = cursor.getColumnIndex(cullom[2]);
		int index_det_eng = cursor.getColumnIndex(cullom[3]);
		int index_mets = cursor.getColumnIndex(cullom[4]);

		int actID = cursor.getInt(index_act_id);
		int headID = cursor.getInt(index_head_id);
		String detJP = cursor.getString(index_det_jp);
		String detENG = cursor.getString(index_det_eng);
		float mets = cursor.getFloat(index_mets);

		cursor.close();
		return new SpecificActivities(actID, headID, detJP, detENG, mets);
	}

	public RecordDate selectRecordDateMatch(int dateID) {
		String[] cullom = new String[] {
				"date_id", "date_cullom"
		};
		String sql = String.format("SELECT %s, %s FROM %s WHERE %s = ?",
				cullom[0], cullom[1],
				TB_RECORD_DATE, cullom[0]);
		Cursor cursor = db.rawQuery(sql, new String[] { String.valueOf(dateID) });
		cursor.moveToFirst();
		if (cursor.getCount() < 1)
			return null;
		int index_date_id = cursor.getColumnIndex(cullom[0]);
		int index_date = cursor.getColumnIndex(cullom[1]);

		int datesID = cursor.getInt(index_date_id);
		String date = cursor.getString(index_date);

		cursor.close();
		return new RecordDate(datesID, date);
	}

	public RecordDate selectRecordDateMatch(String paramDate) {
		String[] cullom = new String[] {
				"date_id", "date_cullom"
		};
		String sql = String.format("SELECT %s, %s FROM %s WHERE %s = ?",
				cullom[0], cullom[1],
				TB_RECORD_DATE, cullom[1]);
		Cursor cursor = db.rawQuery(sql, new String[] { String.valueOf(paramDate)});
		cursor.moveToFirst();
		if (cursor.getCount() < 1)
			return null;
		int index_date_id = cursor.getColumnIndex(cullom[0]);
		int index_date = cursor.getColumnIndex(cullom[1]);

		int datesID = cursor.getInt(index_date_id);
		String date = cursor.getString(index_date);

		cursor.close();
		return new RecordDate(datesID, date);
	}

	public RecordDetail selectRecordDetailMatch(int paramdetailID) {
		String[] cullom = new String[] {
				"detail_id", "date_id", "activities_id","measurement_time",  "heart_rate_theory", "heart_rate_point",
				"calorie_theory", "calorie_point", "graph"
		};
		String sql = String.format("SELECT %s, %s, %s, %s ,%s, %s, %s, %s, %s FROM %s WHERE %s = ?",
				cullom[0], cullom[1], cullom[2], cullom[3], cullom[4],
				cullom[5], cullom[6], cullom[7],cullom[8], TB_RECORD_DETAIL, cullom[0]);
		Cursor cursor = db.rawQuery(sql, new String[] { String.valueOf(paramdetailID) });
		cursor.moveToFirst();
		if (cursor.getCount() < 1)
			return null;

		int index_det_id = cursor.getColumnIndex(cullom[0]);
		int index_date_id = cursor.getColumnIndex(cullom[1]);
		int index_act_id = cursor.getColumnIndex(cullom[2]);
		int index_time = cursor.getColumnIndex(cullom[3]);
		int index_heart_t = cursor.getColumnIndex(cullom[4]);
		int index_heart_p = cursor.getColumnIndex(cullom[5]);
		int index_cal_t = cursor.getColumnIndex(cullom[6]);
		int index_cal_p = cursor.getColumnIndex(cullom[7]);
		int index_graph = cursor.getColumnIndex(cullom[8]);

		int detID = cursor.getInt(index_det_id);
		int dateID = cursor.getInt(index_date_id);
		int actID = cursor.getInt(index_act_id);
		String time = cursor.getString(index_time);
		int heart_t = cursor.getInt(index_heart_t);
		int heart_p = cursor.getInt(index_heart_p);
		float cal_t = cursor.getFloat(index_cal_t);
		float cal_p = cursor.getFloat(index_cal_p);
		byte[] graph = cursor.getBlob(index_graph);

		RecordDetail detail = new RecordDetail(detID, dateID, actID, time, heart_t,heart_p, cal_t,cal_p,graph);

		cursor.close();

		return detail;
	}

	public int insertRecordDate(String date) {
		ContentValues insertValues = new ContentValues();
		insertValues.put("date_cullom", date);

		RecordDate rd = selectRecordDateMatch(date);
		return (rd == null)?
				(int) db.insert(TB_RECORD_DATE, null, insertValues)
				: rd.getID();
	}

	public int insertRecordDetail(int dateID, int activitiesID, String time,int heartRateTheory, int heartRatePoint,
			float calorieTheory, float caloriePoint, byte[] graph) {
		ContentValues insertValues = new ContentValues();
		insertValues.put("date_id", dateID);
		insertValues.put("activities_id", activitiesID);
		insertValues.put("measurement_time", time);
		insertValues.put("heart_rate_theory", heartRateTheory);
		insertValues.put("heart_rate_point", heartRatePoint);
		insertValues.put("calorie_theory", calorieTheory);
		insertValues.put("calorie_point", caloriePoint);
		insertValues.put("graph", graph);

		return (int) db.insert(TB_RECORD_DETAIL, null, insertValues);
	}

	public boolean deleteRecordData(int date_id) {
		return (db.delete(TB_RECORD_DATE, "date_id = ?",
				new String[] { String.valueOf(date_id) }) > -1) ? true : false;
	}

	public boolean deleteRecordDetail(int detail_id) {
		return (db.delete(TB_RECORD_DETAIL, "detaile_id = ?",
				new String[] { String.valueOf(detail_id) }) > -1) ? true : false;
	}

	/*

	public List<ItemRow> selectItemDataAll(int selectClassID) {
		List<ItemRow> list = new ArrayList<ItemRow>();
		String sql = "SELECT selector,answer FROM item WHERE class_id = ?";
		Cursor cursor = db.rawQuery(sql, new String[] { String.valueOf(selectClassID) });
		cursor.moveToFirst();

		boolean flag = true;
		if (cursor.isLast() || cursor.getCount() < 1)
			flag = false;
		while (flag) {
			int index_selector = cursor.getColumnIndex("selector");
			int index_answer = cursor.getColumnIndex("answer");

			String selector = cursor.getString(index_selector);
			String answer = cursor.getString(index_answer);
			list.add(new ItemRow(selector, answer));
			if (cursor.isLast())
				flag = false;
			cursor.moveToNext();
		}
		return list;
	}
	*/
}